﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace GroundWater.DataAccess
{
    internal static class SqlStatements
    {
        // ALTER TABLE WR_GWSWLL_B ADD ( Altitude NUMBER(6,2) DEFAULT 0 )
        // select * from aa where date1>to_date('2008-08-08','yyyy-mm-dd')
        internal const string Users_FetchAllUsers = @"SELECT USERNAME as UserName, USERMS as UserRole, PWD as Password, NOTE as Description FROM SYS_USERS";
        internal const string Users_InsertUser = @"INSERT INTO SYS_USERS ( USERNAME , USERMS , PWD , NOTE ) VALUES (:UserName, :UserRole, :Password, :Description)";
        internal const string Users_UpdateUser = @"UPDATE SYS_USERS SET USERMS = :UserRole, PWD = :Password, NOTE = :Description WHERE USERNAME = :UserName";
        internal const string Users_DeleteUser = @"DELETE FROM SYS_USERS WHERE USERNAME = :UserName";
        internal const string Users_FetchById = @"SELECT USERNAME as UserName, USERMS as UserRole, PWD as Password, NOTE as Description FROM SYS_USERS  WHERE USERNAME = :Id";

        internal const string Users_FetchAuthorizationAll  = @"SELECT USERID as UserId, MENUID as MenuId  FROM SYS_PROFILE";
        internal const string Users_FetchAuthorizationById = @"SELECT MENUID as MenuId  FROM SYS_PROFILE WHERE USERID = :UserId";
        internal const string Users_InsertAuthorization = @"INSERT INTO SYS_PROFILE (USERID, MENUID) VALUES (:UserId, :MenuId)";
        internal const string Users_DeleteAuthorization = @"DELETE FROM SYS_PROFILE WHERE USERID = :UserId AND MENUID = :MenuId";
        internal const string Users_DeleteAuthorizationByUser = @"DELETE FROM SYS_PROFILE WHERE USERID = :UserId";

        internal const string Menus_FetchAll = @"SELECT MENUID as Id, MENUMS as Name, MENUWINDOW as RelativeURL, MENUSORT as SequenceNumber, PARENTMENU as ParentId, DISPLAY_YN as Display FROM SYS_MENU_NEW";



        //insert into sys_users( UserName,UserMs,Pwd ) values
        //internal const string Well_FetchAll = @"SELECT WSWLCD as Code, WSWLNM as Name, LGTD as Longitude FROM WR_GWSWLL_B1 ORDER BY Code";
        //internal const string Well_Update = @"UPDATE WR_GWSWLL_B SET WSWLNM = :Name, SRCCD = :WaterSource, LGTD = :Longitude, LTTD = :Latitude, LANDFM = :Landform, WRRCD = :ResourceRegionCode, ADDVCD = :AdministrativeDivisionsCode, WFRCD = :FunctionRegionCode, DIAMETER = :Diameter, DEPTH = :Depth, INSTDT = :ShaftSinkingDate WHERE WSWLCD = :Code";

        internal const string Well_FetchAll = @"SELECT WSWLCD as Code, WSWLNM as Name, SRCCD as WaterSource, LGTD as Longitude, LTTD as Latitude, LANDFM as Landform, WRRCD as ResourceRegionCode, ADDVCD as AdministrativeDivisionsCode, WFRCD as FunctionRegionCode, DIAMETER as Diameter, DEPTH as Depth, INSTDT as ShaftSinkingDate, PRJRUNST as ProjectRuningState, PUMPTP as PumpType, DYFWQ as DesignYearlyWaterQuantity, DSNQ as DesignFlowRate, SLVL as StaticLevel, DLVL as DynamicLevel, DVDIRNMDVC as RemoveEquipment, WQLST as WaterQuality, ADAG as Administration, NT as SectionPlane, COMMNO as SIMCard, CONTROLLERID as BTUAddress, Z as WaterLevel, SDATE as GatheringDate, Altitude, SequenceNumber, MinimumWaterlevel, MaximumWaterlevel FROM WR_GWSWLL_B ORDER BY Code";
        internal const string Well_FetchById = @"SELECT WSWLCD as Code, WSWLNM as Name, SRCCD as WaterSource, LGTD as Longitude, LTTD as Latitude, LANDFM as Landform, WRRCD as ResourceRegionCode, ADDVCD as AdministrativeDivisionsCode, WFRCD as FunctionRegionCode, DIAMETER as Diameter, DEPTH as Depth, INSTDT as ShaftSinkingDate, PRJRUNST as ProjectRuningState, PUMPTP as PumpType, DYFWQ as DesignYearlyWaterQuantity, DSNQ as DesignFlowRate, SLVL as StaticLevel, DLVL as DynamicLevel, DVDIRNMDVC as RemoveEquipment, WQLST as WaterQuality, ADAG as Administration, NT as SectionPlane, COMMNO as SIMCard, CONTROLLERID as BTUAddress, Z as WaterLevel, SDATE as GatheringDate, Altitude, SequenceNumber, MinimumWaterlevel, MaximumWaterlevel FROM WR_GWSWLL_B WHERE WSWLCD = :Id";
        internal const string Well_FetchName = @"SELECT WSWLCD as Code, WSWLNM as Name FROM WR_GWSWLL_B";
        internal const string Well_FetchAllDivisionsCode = @"SELECT DISTINCT ADDVCD as DivisionsCode FROM WR_GWSWLL_B";

        internal const string Well_Insert = @"INSERT INTO WR_GWSWLL_B ( WSWLCD, WSWLNM, SRCCD, LGTD, LTTD, LANDFM, WRRCD, ADDVCD, WFRCD, DIAMETER, DEPTH, INSTDT, PRJRUNST, PUMPTP, DYFWQ, DSNQ, SLVL, DLVL, DVDIRNMDVC, WQLST, ADAG, NT, COMMNO, CONTROLLERID, Z, SDATE, Altitude, SequenceNumber, MinimumWaterlevel, MaximumWaterlevel ) VALUES (:Code, :Name, :WaterSource, :Longitude, :Latitude, :Landform, :ResourceRegionCode, :AdministrativeDivisionsCode, :FunctionRegionCode, :Diameter, :Depth, :ShaftSinkingDate, :ProjectRuningState, :PumpType, :DesignYearlyWaterQuantity, :DesignFlowRate, :StaticLevel, :DynamicLevel, :RemoveEquipment, :WaterQuality, :Administration, :SectionPlane, :SIMCard, :BTUAddress, :WaterLevel, :GatheringDate, :Altitude, :SequenceNumber, :MinimumWaterlevel, :MaximumWaterlevel)";
        internal const string Well_Update = @"UPDATE WR_GWSWLL_B SET WSWLNM = :Name, SRCCD = :WaterSource, LGTD = :Longitude, LTTD = :Latitude, LANDFM = :Landform, WRRCD = :ResourceRegionCode, ADDVCD = :AdministrativeDivisionsCode, WFRCD = :FunctionRegionCode, DIAMETER = :Diameter, DEPTH = :Depth, INSTDT = :ShaftSinkingDate, PRJRUNST = :ProjectRuningState, PUMPTP = :PumpType, DYFWQ = :DesignYearlyWaterQuantity, DSNQ = :DesignFlowRate, SLVL = :StaticLevel, DLVL = :DynamicLevel, DVDIRNMDVC = :RemoveEquipment, WQLST = :WaterQuality, ADAG = :Administration, NT = :SectionPlane, COMMNO = :SIMCard, CONTROLLERID = :BTUAddress, Z = :WaterLevel, SDATE = :GatheringDate, Altitude = :Altitude, SequenceNumber = :SequenceNumber, MinimumWaterlevel = :MinimumWaterlevel, MaximumWaterlevel =:MaximumWaterlevel WHERE WSWLCD = :Code";
        internal const string Well_Delete = @"DELETE FROM WR_GWSWLL_B WHERE WSWLCD = :Code";

        internal const string WaterSource_FetchAll  = @"SELECT SRCCD as Code, SRCNM as Name, SRCGRD as Grade, ADDVCD as AdministrativeDivisionsCode, WRRCD as ResourceRegionCode, WFRCD as FunctionRegionCode, PRJSCL as ProjectScale, BLDSTT as BuildState, PRDTM as ProductDate, PRJRUNST as ProjectRuningState, WSCNTST as ContinueState, WSPOP as SupplyPopulation, DYSPWQ as YearlyQuality, DMSPWQ as DailyQuality, ADMAG as AdministrationAgency, ADAG as Administration, NT as Note FROM WR_SRCINF_B";
        internal const string WaterSource_FetchById = @"SELECT SRCCD as Code, SRCNM as Name, SRCGRD as Grade, ADDVCD as AdministrativeDivisionsCode, WRRCD as ResourceRegionCode, WFRCD as FunctionRegionCode, PRJSCL as ProjectScale, BLDSTT as BuildState, PRDTM as ProductDate, PRJRUNST as ProjectRuningState, WSCNTST as ContinueState, WSPOP as SupplyPopulation, DYSPWQ as YearlyQuality, DMSPWQ as DailyQuality, ADMAG as AdministrationAgency, ADAG as Administration, NT as Note FROM WR_SRCINF_B WHERE SRCCD = :Id";
        internal const string WaterSource_Insert = @"INSERT INTO WR_SRCINF_B (  SRCCD, SRCNM, SRCGRD, ADDVCD, WRRCD, WFRCD, PRJSCL, BLDSTT, PRDTM, PRJRUNST, WSCNTST, WSPOP, DYSPWQ, DMSPWQ, ADMAG, ADAG, NT ) VALUES (:Code, :Name, :Grade, :AdministrativeDivisionsCode, :ResourceRegionCode, :FunctionRegionCode, :ProjectScale, :BuildState, :ProductDate, :ProjectRuningState, :ContinueState, :SupplyPopulation, :YearlyQuality, :DailyQuality, :AdministrationAgency, :Administration, :Note)";
        internal const string WaterSource_Update = @"UPDATE WR_SRCINF_B SET  SRCNM = :Name, SRCGRD = :Grade, ADDVCD = :AdministrativeDivisionsCode, WRRCD = :ResourceRegionCode, WFRCD = :FunctionRegionCode, PRJSCL = :ProjectScale, BLDSTT = :BuildState, PRDTM = :ProductDate, PRJRUNST = :ProjectRuningState, WSCNTST = :ContinueState, WSPOP = :SupplyPopulation, DYSPWQ = :YearlyQuality, DMSPWQ = :DailyQuality, ADMAG = :AdministrationAgency, ADAG = :Administration, NT = :Note WHERE SRCCD = :Code";
        internal const string WaterSource_Delete = @"DELETE FROM WR_SRCINF_B WHERE SRCCD = :Code";


        internal const string Monitoring_FetchAll = @"SELECT WSWLCD as WellCode, MNTM as MonitorTime, Z as Waterlevel, Q as FlowRate, P as HydraulicPressure, WQG as WaterQuality, NT as SectionPlane, COMMNO as SIMCard, CONTROLLERID as BTUAddress, WSWLNM as WellName, 
      CELL as CellVoltage, Z_ORIGINAL as OriginalWaterLevel FROM WR_GWLSPL_S";

        internal const string Monitoring_FetchById = @"SELECT WSWLCD as WellCode, MNTM as MonitorTime, Z as Waterlevel, Q as FlowRate, P as HydraulicPressure, WQG as WaterQuality, NT as SectionPlane, COMMNO as SIMCard, CONTROLLERID as BTUAddress, WSWLNM as WellName, 
      CELL as CellVoltage, Z_ORIGINAL as OriginalWaterLevel FROM WR_GWLSPL_S WHERE WSWLCD = :WellCode AND MNTM = :MonitorTime";

        internal const string Monitoring_GetById = @"SELECT Id, WSWLCD as WellCode, MNTM as MonitorTime, Z as Waterlevel, Q as FlowRate, P as HydraulicPressure, WQG as WaterQuality, NT as SectionPlane, COMMNO as SIMCard, CONTROLLERID as BTUAddress, WSWLNM as WellName, 
      CELL as CellVoltage, Z_ORIGINAL as OriginalWaterLevel FROM WR_GWLSPL_S WHERE Id = :Id";

        internal const string Monitoring_Insert = @"INSERT INTO WR_GWLSPL_S (WSWLCD, MNTM, Z, Q, P, WQG, NT, COMMNO, CONTROLLERID, WSWLNM, CELL, Z_ORIGINAL)  VALUES (:WellCode, :MonitorTime, :Waterlevel, :FlowRate, :HydraulicPressure, :WaterQuality, :SectionPlane, :SIMCard, :BTUAddress, :WellName, :CellVoltage, :OriginalWaterLevel) ";
        internal const string Monitoring_Update = @"UPDATE WR_GWLSPL_S SET Z = :Waterlevel, Q = :FlowRate, P = :HydraulicPressure, WQG = :WaterQuality, NT = :SectionPlane, COMMNO = :SIMCard, CONTROLLERID = :BTUAddress, WSWLNM = :WellName, CELL = :CellVoltage, Z_ORIGINAL = :OriginalWaterLevel WHERE WSWLCD = :WellCode AND MNTM = :MonitorTime";
        //internal const string Monitoring_Delete = @"DELETE FROM WR_GWLSPL_S WHERE WSWLCD = :Code AND MNTM = :MonitorTime";
        internal const string Monitoring_Delete = @"DELETE FROM WR_GWLSPL_S WHERE Id = :Id";//WSWLCD = :Code AND MNTM = :MonitorTime

        internal const string Monitoring_FetchPaged = @"SELECT Id, WSWLCD as WellCode, WSWLNM as WellName, MNTM as MonitorTime, Z as Waterlevel, CELL as CellVoltage, COMMNO as SIMCard, CONTROLLERID as BTUAddress, NT as SectionPlane, Altitude FROM
( 	SELECT Id, WSWLCD, WSWLNM, MNTM, Z, COMMNO, CONTROLLERID, NT, CELL, Altitude, rownum rn FROM 	(  SELECT m.Id, m.WSWLCD, m.WSWLNM, m.MNTM, m.Z, m.COMMNO, m.CONTROLLERID, m.NT, m.CELL, w.ALTITUDE - m.Z as Altitude from WR_GWLSPL_S m INNER JOIN WR_GWSWLL_B w ON m.WSWLCD = w.WSWLCD
		WHERE m.WSWLCD = '{0}' AND CAST(m.MNTM AS DATE) >=  TO_DATE('{1}', 'yyyy-mm-dd') AND CAST(m.MNTM AS DATE) < TO_DATE('{2}', 'yyyy-mm-dd')
		ORDER BY {3} 	) tmp ) t WHERE rn BETWEEN {4} AND {5}";

        internal const string Monitoring_FetchRowCount = @"SELECT COUNT(*) FROM WR_GWLSPL_S WHERE WSWLCD = '{0}' AND CAST(MNTM AS DATE) >= TO_DATE('{1}', 'yyyy-mm-dd') AND CAST(MNTM AS DATE) < TO_DATE('{2}', 'yyyy-mm-dd')";

        internal const string Monitoring_FetchSectionPlane = @"SELECT DISTINCT NT as SectionPlane FROM WR_GWLSPL_S";

        internal const string Monitoring_FetchWaterLeveGroupByWellCode = @"SELECT WSWLCD as WellCode , round(AVG(Z), 2) as Waterlevel 
FROM WR_GWLSPL_S 
WHERE NT = '{0}' AND  CAST(MNTM AS DATE) >=  TO_DATE('{1}', 'yyyy-mm-dd') AND CAST(MNTM AS DATE) <  TO_DATE('{2}', 'yyyy-mm-dd') 
GROUP BY WSWLCD ";

        internal const string Monitoring_FetchWaterLeveGroupByDate = @"SELECT MonitorTime, round(AVG(Waterlevel), 2) as Waterlevel 
FROM (SELECT WSWLCD as WellCode, TO_CHAR(MNTM, 'yyyy-MM-dd') as MonitorTime, Z as Waterlevel 
FROM WR_GWLSPL_S WHERE WSWLCD = '{0}' 
AND CAST(MNTM AS DATE) >= TO_DATE('{1}', 'yyyy-mm-dd') AND CAST(MNTM AS DATE) < TO_DATE('{2}', 'yyyy-mm-dd')) tmp 
GROUP BY MonitorTime 
ORDER BY MonitorTime ";


        internal const string Monitoring_FetchByDatePaged = @"SELECT WSWLNM as WellName, MNTM as MonitorTime, Z as Waterlevel, CELL as CellVoltage, COMMNO as SIMCard, CONTROLLERID as BTUAddress, NT as SectionPlane, Z_ORIGINAL as OriginalWaterLevel, Altitude FROM
(
	SELECT WSWLNM, MNTM, Z, COMMNO, CONTROLLERID, NT, CELL, Z_ORIGINAL, ALTITUDE, rownum rn FROM
	(
		SELECT m.WSWLNM, m.MNTM, m.Z, m.COMMNO, m.CONTROLLERID, m.NT, m.CELL, m.Z_ORIGINAL, w.ALTITUDE - m.Z as Altitude from WR_GWLSPL_S m INNER JOIN WR_GWSWLL_B w ON m.WSWLCD = w.WSWLCD
		WHERE CAST(m.MNTM AS DATE) >=  TO_DATE('{0}', 'yyyy-mm-dd') AND CAST(m.MNTM AS DATE) < TO_DATE('{1}', 'yyyy-mm-dd')
		ORDER BY {2}
	) tmp
) t WHERE rn BETWEEN {3} AND {4}";


        internal const string Monitoring_FetchByDateRowCount = @"SELECT COUNT(*) FROM WR_GWLSPL_S WHERE CAST(MNTM AS DATE) >=  TO_DATE('{0}', 'yyyy-mm-dd') AND CAST(MNTM AS DATE) < TO_DATE('{1}', 'yyyy-mm-dd')";

        internal const string Monitoring_FetchWaterLeveByWellCode = @"SELECT WSWLCD as WellCode , round(AVG(Z), 2) as Waterlevel 
FROM WR_GWLSPL_S 
WHERE WSWLCD = '{0}' AND  CAST(MNTM AS DATE) >=  TO_DATE('{1}', 'yyyy-mm-dd') AND CAST(MNTM AS DATE) <  TO_DATE('{2}', 'yyyy-mm-dd') 
GROUP BY WSWLCD";

        internal const string Monitoring_FetchWaterLeveAllByDate = @"SELECT WSWLCD as WellCode, round(AVG(Z), 2) as Waterlevel 
FROM WR_GWLSPL_S 
WHERE  CAST(MNTM AS DATE) >=  TO_DATE('{0}', 'yyyy-mm-dd') AND CAST(MNTM AS DATE) <  TO_DATE('{1}', 'yyyy-mm-dd') 
GROUP BY WSWLCD ORDER BY WSWLCD";

//        internal const string Monitoring_FetchWaterLeveBySectionPlane = @"SELECT NT as SectionPlane , round(AVG(Z), 2) as Waterlevel 
//FROM WR_GWLSPL_S 
//WHERE NT = '{0}' AND  CAST(MNTM AS DATE) >=  TO_DATE('{1}', 'yyyy-mm-dd') AND CAST(MNTM AS DATE) <  TO_DATE('{2}', 'yyyy-mm-dd') 
//GROUP BY NT";

        internal const string Monitoring_FetchWaterLeveBySectionPlane = @"SELECT WellCode, Waterlevel FROM (
SELECT WSWLCD  WellCode , round(AVG(Z), 2)  Waterlevel 
FROM WR_GWLSPL_S 
WHERE NT = '{0}' AND  CAST(MNTM AS DATE) >=  TO_DATE('{1}', 'yyyy-mm-dd') AND CAST(MNTM AS DATE) <  TO_DATE('{2}', 'yyyy-mm-dd') 
GROUP BY WSWLCD ) tmp
INNER JOIN WR_GWSWLL_B w ON w.WSWLCD = tmp.WellCode
ORDER BY w.SequenceNumber";


        internal const string Monitoring_FetchWaterLeveAllBySectionPlane = @"SELECT NT as SectionPlane , round(AVG(Z), 2) as Waterlevel 
FROM WR_GWLSPL_S 
WHERE CAST(MNTM AS DATE) >=  TO_DATE('{0}', 'yyyy-mm-dd') AND CAST(MNTM AS DATE) <  TO_DATE('{1}', 'yyyy-mm-dd') 
GROUP BY NT ORDER BY NT ";

        // 按行政区划分组
        internal const string Monitoring_FetchWaterLeveByDivisionsCode = @"SELECT DivisionsCode, Waterlevel
FROM ( SELECT CAST(w.ADDVCD AS INT ) as DivisionsCode , round(AVG(m.Z), 2) as Waterlevel 
FROM WR_GWLSPL_S m INNER JOIN WR_GWSWLL_B w ON m.WSWLCD = w.WSWLCD
WHERE CAST(m.MNTM AS DATE) >=  TO_DATE('{0}', 'yyyy-mm-dd') AND CAST(m.MNTM AS DATE) <  TO_DATE('{1}', 'yyyy-mm-dd') 
GROUP BY w.ADDVCD ) tmp
ORDER BY DivisionsCode ";

        // 某个行政区域下按水井分组统计水位
        internal const string Monitoring_FetchWaterLeveAvgByDivisionsCode = @"SELECT m.WSWLCD  WellCode, round(AVG(m.Z), 2)  Waterlevel 
FROM WR_GWLSPL_S m INNER JOIN WR_GWSWLL_B w ON m.WSWLCD = w.WSWLCD
WHERE CAST(m.MNTM AS DATE) >=  TO_DATE('{0}', 'yyyy-mm-dd') AND CAST(m.MNTM AS DATE) <  TO_DATE('{1}', 'yyyy-mm-dd') 
AND w.ADDVCD = '{2}'
GROUP BY m.WSWLCD ";

        internal const string SectionPlane_FetchAll = @"SELECT Id, Name, Code, QueryCode, Description FROM BaseInfo_SectionPlane ORDER BY Code";
        internal const string SectionPlane_FetchById = @"SELECT Id, Name, Code, QueryCode, Description FROM BaseInfo_SectionPlane WHERE Id = :Id";
        internal const string SectionPlane_Insert = @"INSERT INTO BaseInfo_SectionPlane ( Id, Name, Code, QueryCode, Description ) VALUES ( :Id, :Name, :Code, :QueryCode, :Description)";
        internal const string SectionPlane_Update = @"UPDATE BaseInfo_SectionPlane SET Name = :Name, Code = :Code, QueryCode = :QueryCode, Description = :Description WHERE Id = :Id";
        internal const string SectionPlane_Delete = @"DELETE FROM BaseInfo_SectionPlane WHERE Id = :Id";



        internal const string Monitoring_FetchByWellPaged = @"SELECT WSWLNM as WellName, MNTM as MonitorTime, Z as Waterlevel, CELL as CellVoltage, COMMNO as SIMCard, CONTROLLERID as BTUAddress, NT as SectionPlane, Z_ORIGINAL as OriginalWaterLevel, ALTITUDE as Altitude FROM
(
	SELECT WSWLNM, MNTM, Z, COMMNO, CONTROLLERID, NT, CELL, Z_ORIGINAL, ALTITUDE, rownum rn FROM
	(
		SELECT WSWLNM, MNTM, Z, COMMNO, CONTROLLERID, NT, CELL, Z_ORIGINAL, ALTITUDE from WR_GWLSPL_S m INNER JOIN WR_GWSWLL_B w ON m.WSWLCD = w.WSWLCD
		WHERE WSWLCD = '{0}' AND  CAST(MNTM AS DATE) >=  TO_DATE('{1}', 'yyyy-mm-dd')
			AND CAST(MNTM AS DATE) < TO_DATE('{2}', 'yyyy-mm-dd')
		ORDER BY m.{3}
	) tmp
) WHERE rn BETWEEN {4} AND {5}";
        internal const string Monitoring_FetchByWellRowCount = @"SELECT COUNT(*) FROM WR_GWLSPL_S WHERE WSWLCD = '{0}' AND  CAST(MNTM AS DATE) >=  TO_DATE('{1}', 'yyyy-mm-dd') AND CAST(MNTM AS DATE) < TO_DATE('{2}', 'yyyy-mm-dd')";

        internal const string Monitoring_FetchByPlanePaged = @"SELECT WSWLNM as WellName, MNTM as MonitorTime, Z as Waterlevel, CELL as CellVoltage, COMMNO as SIMCard, CONTROLLERID as BTUAddress, NT as SectionPlane, Z_ORIGINAL as OriginalWaterLevel FROM
(
	SELECT WSWLNM, MNTM, Z, COMMNO, CONTROLLERID, NT, CELL, Z_ORIGINAL, rownum rn FROM
	(
		SELECT WSWLNM, MNTM, Z, COMMNO, CONTROLLERID, NT, CELL, Z_ORIGINAL from WR_GWLSPL_S m
		WHERE NT = '{0}' AND CAST(MNTM AS DATE) >=  TO_DATE('{0}', 'yyyy-mm-dd') AND CAST(MNTM AS DATE) < TO_DATE('{1}', 'yyyy-mm-dd')
		ORDER BY m.{2}
	) tmp
) WHERE rn BETWEEN {3} AND {4}";
        internal const string Monitoring_FetchByPlaneRowCount = @"SELECT COUNT(*) FROM WR_GWLSPL_S WHERE CAST(MNTM AS DATE) >=  TO_DATE('{0}', 'yyyy-mm-dd') AND CAST(MNTM AS DATE) < TO_DATE('{1}', 'yyyy-mm-dd')";

        //internal const string Monitoring_FetchToday = @"SELECT m.WSWLCD as WellCode, m.WSWLNM as WellName, m.MNTM as MonitorTime, m.Z as Waterlevel, m.CELL as CellVoltage, m.COMMNO as SIMCard, m.CONTROLLERID as BTUAddress, m.NT as SectionPlane, (w.ALTITUDE - m.Z) as Altitude FROM WR_GWLSPL_S m INNER JOIN WR_GWSWLL_B w ON m.WSWLCD = w.WSWLCD WHERE m.MNTM = (SELECT MAX(MNTM) as Waterlevel FROM WR_GWLSPL_S)";
        internal const string Monitoring_FetchToday = @"SELECT m.WSWLCD as WellCode, m.WSWLNM as WellName, m.MNTM as MonitorTime, m.Z as Waterlevel, m.CELL as CellVoltage, m.COMMNO as SIMCard, m.CONTROLLERID as BTUAddress, m.NT as SectionPlane, m.Z_ORIGINAL as OriginalWaterLevel, (w.ALTITUDE - m.Z) as Altitude FROM WR_GWLSPL_S m INNER JOIN WR_GWSWLL_B w ON m.WSWLCD = w.WSWLCD WHERE CAST(m.MNTM AS DATE) >=  TO_DATE('{0}', 'yyyy-mm-dd') AND CAST(m.MNTM AS DATE) < TO_DATE('{1}', 'yyyy-mm-dd') order by m.WSWLCD, m.MNTM DESC";

        internal const string Abnormal_FetchById = @"SELECT Id, PreviousWaterlevel, CurrentWaterlevel, Difference, WarningDifference, MonitoringDate, WellCode, MonitoringId, ExamineDate, Examiner FROM WR_WaterlevelAbnormal WHERE Id = :Id";
        internal const string Abnormal_FetchAll = @"SELECT Id, PreviousWaterlevel, CurrentWaterlevel, Difference, WarningDifference, MonitoringDate, WellCode, MonitoringId, ExamineDate, Examiner FROM WR_WaterlevelAbnormal";
        internal const string Abnormal_FetchByMonitoringId = @"SELECT Id, PreviousWaterlevel, CurrentWaterlevel, Difference, WarningDifference, MonitoringDate, WellCode, MonitoringId, ExamineDate, Examiner FROM WR_WaterlevelAbnormal WHERE MonitoringDate = :MonitoringDate AND WellCode = :WellCode AND WarningDifference >= :WarningDifference";
        internal const string Abnormal_FetchPaged = @"SELECT Id, PreviousWaterlevel, CurrentWaterlevel, Difference, WarningDifference, MonitoringDate, WellCode, MonitoringId, ExamineDate, Examiner, WellName 
FROM  ( SELECT Id, PreviousWaterlevel, CurrentWaterlevel, Difference, WarningDifference, MonitoringDate, WellCode, MonitoringId, ExamineDate, Examiner, WellName, rownum rn
	FROM ( SELECT Id, PreviousWaterlevel, CurrentWaterlevel, Difference, WarningDifference, MonitoringDate, WellCode, MonitoringId, ExamineDate, Examiner, w.WSWLNM as WellName 
FROM WR_WaterlevelAbnormal a  INNER JOIN WR_GWSWLL_B w ON a.WellCode = w.WSWLCD WHERE ExamineDate >=  TO_DATE('{0}', 'yyyy-mm-dd') AND ExamineDate < TO_DATE('{1}', 'yyyy-mm-dd')  ORDER BY {2}) tmp 
 ) t WHERE rn BETWEEN {3} AND {4}";
        internal const string Abnormal_FetchRowCount = @"SELECT COUNT(*) FROM WR_WaterlevelAbnormal WHERE ExamineDate >=  TO_DATE('{0}', 'yyyy-mm-dd') AND MonitoringDate < TO_DATE('{1}', 'yyyy-mm-dd')";

        internal const string Abnormal_Insert = @"INSERT INTO WR_WaterlevelAbnormal ( Id, PreviousWaterlevel, CurrentWaterlevel, Difference, WarningDifference, MonitoringDate, WellCode, MonitoringId, ExamineDate, Examiner ) VALUES ( WR_WATERLEVELABNORMAL_SEQ.nextval, :PreviousWaterlevel, :CurrentWaterlevel, :Difference, :WarningDifference, :MonitoringDate, :WellCode, :MonitoringId, :ExamineDate, :Examiner)";
        internal const string Abnormal_Update = @"UPDATE WR_WaterlevelAbnormal SET PreviousWaterlevel = :PreviousWaterlevel, CurrentWaterlevel = :CurrentWaterlevel, Difference = :Difference, WarningDifference = :WarningDifference, MonitoringDate = :MonitoringDate, WellCode = :WellCode, MonitoringId = :MonitoringId, ExamineDate = :ExamineDate, Examiner = :Examiner WHERE Id = :Id";
        internal const string Abnormal_Delete = @"DELETE FROM WR_WaterlevelAbnormal WHERE Id = :Id";

        internal const string Abnormal_FetchByTimeSpan = @"SELECT Id, PreviousWaterlevel, CurrentWaterlevel, Difference, WarningDifference, MonitoringDate, WellCode, MonitoringId, ExamineDate, Examiner, w.WSWLNM as WellName FROM WR_WaterlevelAbnormal a INNER JOIN WR_GWSWLL_B w ON a.WellCode = w.WSWLCD  WHERE ExamineDate >=  TO_DATE(:StartMonitoringDate, 'yyyy-mm-dd') AND ExamineDate < TO_DATE(:EndMonitoringDate, 'yyyy-mm-dd') AND WarningDifference = :WarningDifference";

        internal const string Monitoring_FetchByDate = @"SELECT WSWLCD as WellCode, WSWLNM as WellName, MNTM as MonitorTime, Z as Waterlevel FROM WR_GWLSPL_S m WHERE  CAST(MNTM AS DATE) >=  TO_DATE('{0}', 'yyyy-mm-dd') AND CAST(MNTM AS DATE) < TO_DATE('{1}', 'yyyy-mm-dd') ORDER BY WSWLCD, MNTM DESC";

        internal const string Monitoring_FetchCriteria = @"SELECT m.WSWLCD as WellCode, m.WSWLNM as WellName, m.MNTM as MonitorTime, m.Z as Waterlevel, m.CELL as CellVoltage, m.COMMNO as SIMCard, m.CONTROLLERID as BTUAddress, m.NT as SectionPlane, m.Z_ORIGINAL as OriginalWaterLevel, (w.ALTITUDE - m.Z) as Altitude, w.MinimumWaterlevel, w.MaximumWaterlevel FROM WR_GWLSPL_S m INNER JOIN WR_GWSWLL_B w ON m.WSWLCD = w.WSWLCD WHERE CAST(m.MNTM AS DATE) >=  TO_DATE('{0}', 'yyyy-mm-dd') AND CAST(m.MNTM AS DATE) < TO_DATE('{1}', 'yyyy-mm-dd') {2} order by m.WSWLCD, m.MNTM DESC";

        internal const string Monitoring_FetchDataByDate = @"SELECT WSWLCD as WellCode, MNTM as MonitorTime, Z as Waterlevel FROM WR_GWLSPL_S m WHERE IsConcurrency = 0 AND CAST(MNTM AS DATE) >=  TO_DATE('{0}', 'yyyy-mm-dd HH24:mi:ss') AND CAST(MNTM AS DATE) < TO_DATE('{1}', 'yyyy-mm-dd HH24:mi:ss') ORDER BY WSWLCD, MNTM DESC";

        internal const string Monitoring_UpdateConcurrencyByDate = @"UPDATE WR_GWLSPL_S SET IsConcurrency = 1 WHERE IsConcurrency = 0 AND CAST(MNTM AS DATE) >=  TO_DATE('{0}', 'yyyy-mm-dd HH24:mi:ss') AND CAST(MNTM AS DATE) < TO_DATE('{1}', 'yyyy-mm-dd HH24:mi:ss')";


    }
}
